package coba.mailing.query;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

import coba.mailing.model.Atractivo;

public class FindAtractivoByItinerarioIdQuery {

	public static List<Atractivo> find(Connection con, Long itinerarioId) throws SQLException {

		String sql = " select * from COBA_ItinerarioRecorridos ir inner join COBA_Recorridos r on ir.recorridoId = r.id_ "
				+ " and ir.itinerarioId = ? order by hora and recorridoId ";

		PreparedStatement stmt = con.prepareStatement(sql);
		
		stmt.setLong(1, itinerarioId);

		ResultSet rs = stmt.executeQuery();

		List<Atractivo> list = new LinkedList<Atractivo>();

		while (rs.next()) {
			Atractivo obj = new Atractivo(
					rs.getLong("itinerarioId"),
					rs.getLong("recorridoId"),
					rs.getString("nombre"),
					rs.getString("mails"),
					rs.getLong("hora"));

			list.add(obj);
		}

		rs.close();
		stmt.close();

		return list;

	}

}
